﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Common;
using Utils.DC.Web.UI;
using System.Data.OleDb;
using ExcelXml;
using Utils;

public partial class _Default : System.Web.UI.Page
{
    DataSet dataSet;
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    public DataSet LoadFile(String fileName, String sheetName)
    {
        
        if (fileName == null || fileName.Trim().Equals("") ||
            sheetName == null || sheetName.Trim().Equals(""))
        {
            return null;
        }
        string connectionString = string.Empty;
        DbProviderFactory dbFactories = DbProviderFactories.GetFactory("System.Data.OleDb");
        DbConnection con;        

        con = dbFactories.CreateConnection();
        connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
        con.ConnectionString = connectionString;

        DbDataAdapter adapter = dbFactories.CreateDataAdapter();
        DbCommand selectCommand = con.CreateCommand();

        if (!sheetName.EndsWith("$"))
        {
            sheetName += "$";
        }

        selectCommand.CommandText = "SELECT * FROM [" + sheetName + "]";
        selectCommand.Connection = con;
        try
        {
            con.Open();
            adapter.SelectCommand = selectCommand;
            dataSet = new DataSet();
            adapter.Fill(dataSet);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
        }
        return dataSet;

    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (!fupAspNetUpload.HasFile)
        {
            Label1.Text = "Chọn một file ";
            return;
        }
        if (tbxSheetName.Text.Trim() == "")
        {
            Label1.Text = "Nhập tên Sheet ";
            return;
        }

        try
        {
            string filename = ConfigurationManager.ConnectionStrings["UploadFolder"].ConnectionString + fupAspNetUpload.FileName;
            fupAspNetUpload.SaveAs(filename);

            Label1.Text = "File name: " + fupAspNetUpload.PostedFile.FileName + "<br>" +
                            "Size: " + fupAspNetUpload.PostedFile.ContentLength + " kb<br>" +
                            "Content type: " + fupAspNetUpload.PostedFile.ContentType;

            //start reading
            dataSet = LoadFile(filename, tbxSheetName.Text);
            //end reading

            if (dataSet == null)
            {
                Label1.Text = filename + "not found";
                return;
            }

            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                string name = row["Name"].ToString();
                string address = row["Address"].ToString(); ;
                DateTime birthday = DateTime.Parse(row["Birthday"].ToString());
                string description = row["Description"].ToString();
                string email = row["Email"].ToString();
                string experience = row["Experience"].ToString();
                int idnumber = int.Parse(row["IDNumber"].ToString());
                string mark = row["Mark"].ToString();
                string phone = row["Phone"].ToString();
                string place = row["Place"].ToString();
                double salary = double.Parse(row["Salary"].ToString());
                string specialize = row["Specialize"].ToString();
                string subject = row["Subjects"].ToString();
                string university = row["University"].ToString();
                string strURL = row["Image"].ToString();
                string tutorclass = row["Class"].ToString();
                string tutortime = row["Time"].ToString();
                int education;
                bool gender;
                int job;
                int trans;

                switch (row["Education"].ToString())
                {
                    case "Trung cấp": education = 0; break;
                    case "Cao đẳng": education = 1; break;
                    case "Đại học": education = 2; break;
                    default: education = 3; break;
                }

                if (row["Gender"].ToString() == "Nam")
                {
                    gender = true;
                }
                else { gender = false; }

                switch (row["Job"].ToString())
                {
                    case "Sinh Viên": job = 0; break;
                    case "Giáo viên": job = 1; break;
                    default: job = 2; break;
                }

                switch (row["Transportation"].ToString())
                {
                    case "Xe đạp": trans = 0; break;
                    case "Xe máy": trans = 1; break;
                    case "Xe bus": trans = 2; break;
                    default: trans = 3; break;
                }
                new DCEdu.TutorProfile().Add(name, gender, idnumber, address, birthday, phone, email, education, university,
                    specialize, job, mark, strURL, subject, tutorclass, trans, experience, salary, place, tutortime, description);
            }

            GridView1.AllowPaging = true;
            GridView1.AllowSorting = true;
            GridView1.DataSource = dataSet;
            GridView1.DataBind();

        }
        catch (Exception ex)
        {
            MessageBox msgbox = new MessageBox();
            msgbox.Alert(ex.ToString());
        }

        
        
    }


}
